package com.info.zhiduoduo.repository.mapper.crowdsource;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.info.zhiduoduo.repository.dto.crowdsource.ContractorInvoicefDto;
import com.info.zhiduoduo.repository.dto.crowdsource.InvoiceDetailsDto;
import com.info.zhiduoduo.repository.dto.crowdsource.TaxClearanceCertificateDto;
import com.info.zhiduoduo.repository.entity.crowdsource.ContractorInvoice;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;

/**
 * @author tianyonghong
 * @create 2022/3/24
 */
@Mapper
public interface ContractorInvoiceMapper extends BaseMapper<ContractorInvoice> {

    @Select("SELECT zci.*, csp.channel,csp.project_total_amount,csp.project_total_service_charge,csp.project_total_payable_amount,csp.pay_type,  " +
            " cspi.project_service_charge,cspi.integrated_tax_fee,ccp.company_name AS subjectName, cc.tax_source_name as invoiceCityName, " +
            " csp.crowdsource_city,zts.tax_source_name " +
            " FROM zdd_contractor_invoice zci   " +
            " LEFT JOIN zdd_crowdsource_project csp ON csp.projectid = zci.projectid AND csp.deleted = 0    " +
            " LEFT JOIN zdd_crowdsource_project_item cspi ON cspi.id = zci.project_item_id  " +
            " LEFT JOIN zdd_crowdsource_company_party ccp ON ccp.id = zci.party_id   " +
            " LEFT JOIN zdd_tax_source cc ON cc.tax_source_id = zci.invoice_city_id   " +
            " LEFT JOIN zdd_company c ON c.compid = zci.compid   " +
            " LEFT JOIN zdd_tax_source zts ON zts.tax_source_id = csp.tax_source_id " +
            " WHERE zci.createtime between #{startDate} and #{endDate} " +
            " AND IF(ISNULL(#{compname}) OR #{compname} = '', 1=1, c.compname like '%${compname}%') " +
            " AND if(#{state} = 0, 1=1, zci.state = #{state}) " +
            " ORDER BY zci.finishtime desc ")
    List<ContractorInvoicefDto> contractorInvoiceList(@Param("compname") String compname,@Param("state") int state,
                                                      @Param("startDate") Date startDate, @Param("endDate") Date endDate);

    @Select("SELECT COUNT(invoice_id)  " +
            "FROM zdd_contractor_invoice " +
            "WHERE state in(1,2) " +
            "AND createtime < #{date} ")
    int reminderOfOverdueCost(@Param("date") Date date);

    @Select("SELECT * " +
            "FROM zdd_contractor_invoice " +
            "WHERE state = 3 " +
            "AND DATE_FORMAT(finishtime,'%Y-%m') = #{date} " +
            "GROUP BY invoice_city_id,party_id ")
    List<ContractorInvoice> overdueTaxForm(@Param("date") String date);

    @Select("SELECT zci.*, csp.channel,csp.project_total_amount,csp.project_total_service_charge,csp.project_total_payable_amount,csp.pay_type,  " +
            " cspi.project_service_charge,cspi.integrated_tax_fee,ccp.company_name AS subjectName, cc.tax_source_name as invoiceCityName, " +
            " csp.crowdsource_city,csp.tax_source_id,zts.tax_source_name " +
            " FROM zdd_contractor_invoice zci   " +
            " LEFT JOIN zdd_crowdsource_project csp ON csp.projectid = zci.projectid AND csp.deleted = 0    " +
            " LEFT JOIN zdd_crowdsource_project_item cspi ON cspi.id = zci.project_item_id  " +
            " LEFT JOIN zdd_crowdsource_company_party ccp ON ccp.id = zci.party_id   " +
            " LEFT JOIN zdd_tax_source cc ON cc.tax_source_id = zci.invoice_city_id   " +
            " LEFT JOIN zdd_company c ON c.compid = zci.compid   " +
            " LEFT JOIN zdd_tax_source zts ON zts.tax_source_id = csp.tax_source_id " +
            " WHERE zci.createtime between #{startDate} and #{endDate} " +
            " AND IF(ISNULL(#{compname}) OR #{compname} = '', 1=1, ccp.company_name like '%${compname}%') " +
            " AND if(#{state} = 0, 1=1, zci.state = #{state}) " +
            " AND if(ISNULL(#{taxSourceId}) or #{taxSourceId} = '', 1=1, csp.tax_source_id IN(0${taxSourceId}) ) " +
            " AND if(#{itemType} = 0,1=1,csp.work_type = #{itemType})  " +
            " AND if(ISNULL(#{whetherToSupplement}),1=1,csp.supplement = #{whetherToSupplement})  " +
            " ORDER BY zci.createtime desc ")
    List<ContractorInvoicefDto> smartGigBusinessInvoiceList(@Param("compname") String compname,
                                                            @Param("state") int state,
                                                            @Param("startDate") Date startDate,
                                                            @Param("endDate") Date endDate,
                                                            @Param("itemType")Integer itemType,
                                                            @Param("whetherToSupplement")Integer whetherToSupplement,
                                                            @Param("taxSourceId")String taxSourceId);

    @Select("SELECT zci.apply_amount,zci.createtime,zci.invoice_content,zci.invoice_picture,ccp.*, " +
            " cp.tax_source_cost_rate " +
            " FROM zdd_contractor_invoice zci " +
            " LEFT JOIN zdd_crowdsource_company_party ccp ON ccp.id = zci.party_id   " +
            " LEFT JOIN zdd_crowdsource_project cp ON cp.projectid = zci.projectid   " +
            " WHERE zci.invoice_id = #{invoiceId} ")
    InvoiceDetailsDto invoiceDetails(@Param("invoiceId") Integer invoiceId);

    @Select("SELECT zts.affiliated_park as drawer, zci.apply_amount,zci.createtime,zci.invoice_content,zci.invoice_picture,ccp.*" +
            " FROM zdd_contractor_invoice zci   " +
            " LEFT JOIN zdd_crowdsource_project csp ON csp.projectid = zci.projectid AND csp.deleted = 0    " +
            " LEFT JOIN zdd_crowdsource_project_item cspi ON cspi.id = zci.project_item_id  " +
            " LEFT JOIN zdd_crowdsource_company_party ccp ON ccp.id = zci.party_id   " +
            " LEFT JOIN zdd_tax_source cc ON cc.tax_source_id = zci.invoice_city_id   " +
            " LEFT JOIN zdd_company c ON c.compid = zci.compid   " +
            " LEFT JOIN zdd_tax_source zts ON zts.tax_source_id = csp.tax_source_id " +
            " WHERE zci.createtime between #{startDate} and #{endDate} " +
            " AND IF(ISNULL(#{compname}) OR #{compname} = '', 1=1, ccp.company_name like '%${compname}%') " +
            " AND if(#{state} = 0, 1=1, zci.state = #{state}) " +
            " AND if(ISNULL(#{taxSourceId}) or #{taxSourceId} = '', 1=1, csp.tax_source_id IN(0${taxSourceId}) ) " +
            " AND if(#{itemType} = 0,1=1,csp.work_type = #{itemType})  " +
            " AND if(ISNULL(#{whetherToSupplement}),1=1,csp.supplement = #{whetherToSupplement})  " +
            " ORDER BY zci.createtime desc ")
    List<InvoiceDetailsDto> deriveSmartGigBusinessInvoice(@Param("compname") String compname,
                                                          @Param("state") int state,
                                                          @Param("startDate") Date startDate,
                                                          @Param("endDate") Date endDate,
                                                          @Param("itemType")Integer itemType,
                                                          @Param("whetherToSupplement")Integer whetherToSupplement,
                                                          @Param("taxSourceId")String taxSourceId);
    @Select("SELECT " +
            " ts.tax_source_name, " +
            " ccp.company_name, " +
            " DATE_FORMAT( ctri.finishtime, '%Y-%m' ) AS date, " +
            " SUM( ctri.tradeamount ) AS invoicedAmount  " +
            "FROM " +
            " zdd_crowdsource_trade_record_item ctri " +
            " LEFT JOIN zdd_crowdsource_project cp ON cp.projectid = ctri.projectid " +
            " LEFT JOIN zdd_tax_source ts ON ts.tax_source_id = cp.tax_source_id " +
            " LEFT JOIN zdd_crowdsource_company_party ccp ON ccp.id = cp.party_id " +
            " LEFT JOIN zdd_tax_payment_voucher tpv ON tpv.tax_source_id = cp.tax_source_id " +
            " AND tpv.operating_entity_id = cp.party_id " +
            " AND tpv.document_date = DATE_FORMAT( ctri.finishtime, '%Y-%m' )  " +
            "WHERE ctri.`status` = 2 " +
            "  AND IF(ISNULL(#{taxSourceId}) ,1=1,cp.tax_source_id = #{taxSourceId})    " +
            "  AND IF(ISNULL(#{operatingEntityId}) ,1=1,cp.party_id = #{operatingEntityId}) " +
            "  AND IF(ISNULL(#{date}) ,1=1,DATE_FORMAT( ctri.finishtime,'%Y-%m') < #{date} ) " +
            "  AND tpv.id IS NULL  " +
            "  AND ts.tax_source_name IS NOT NULL " +
            "  AND cp.work_type = 2 " +
            "  AND cp.deleted = 0 " +
            "  AND cp.compid != 75 " +
            "GROUP BY " +
            " cp.tax_source_id, " +
            " cp.party_id, " +
            " DATE_FORMAT( ctri.finishtime, '%Y-%m' )  " +
            "HAVING " +
            " invoicedAmount != 0  " +
            "ORDER BY " +
            " DATE_FORMAT( ctri.finishtime, '%Y-%m' ) DESC")
    List<TaxClearanceCertificateDto> taxClearanceCertificate(@Param("taxSourceId") Integer taxSourceId,
                                                             @Param("operatingEntityId") Integer operatingEntityId,
                                                             @Param("date") String date);
}
